﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class UoMManager:ConnectionManager
    {
        public void AddMeasure(UoMUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO UnitMeasures (ItemCode, ItemName, UoM, Dscription, BarCode, SmallestUoMConv, ConvQty, DefaultUoM) VALUES (@ItemCode, @ItemName, @UnitMeasure, @Dscription, @BarCode, @SmallestUoMConv, @ConvQty, @DefaultUoM)";
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", oUnit.ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemName", oUnit.ItemName));
            oCommand.Parameters.Add(new SqlParameter("@UnitMeasure", oUnit.UnitMeasure));
            oCommand.Parameters.Add(new SqlParameter("@Dscription", oUnit.Description));
            oCommand.Parameters.Add(new SqlParameter("@BarCode", oUnit.BarCode));
            oCommand.Parameters.Add(new SqlParameter("@ConvQty", oUnit.ConversionQty));
            oCommand.Parameters.Add(new SqlParameter("@SmallestUoMConv", oUnit.SmallestConversionQty));
            oCommand.Parameters.Add(new SqlParameter("@DefaultUoM", oUnit.DefaultUoM));


            oCommand.ExecuteNonQuery();
        }

        public void UpdateMeasure(DataTable _UNITMEASURES)
        {
            SqlCommand oCommand = new SqlCommand();

            foreach (DataRow oRow in _UNITMEASURES.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE UNITMEASURES SET ItemCode=@ItemCode, ItemName=@ItemName, UoM=@UoM, Dscription=@Dscription, BarCode=@BarCode, ConvQty=@ConvQty, SmallestUoMConv=@SmallestUoMConv, DefaultUoM=@DefaultUoM WHERE RecIndex=@RecIndex";

                oCommand.Parameters.Add(new SqlParameter("@RecIndex", Convert.ToInt16(oRow["RecIndex"])));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Dscription", oRow["Dscription"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@BarCode", oRow["BarCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ConvQty", oRow["ConvQty"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@SmallestUoMConv", oRow["SmallestUoMConv"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@DefaultUoM", oRow["DefaultUoM"].ToString()));
                oCommand.ExecuteNonQuery();
            }

            
        }


        public bool IsUoMExists(string _ITEMCODE, string _UOM)
        {
            DataTable _table = new DataTable();

            SqlConnection oConnection = new SqlConnection();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "SELECT * FROM UnitMeasures WHERE ItemCode=@ItemCode AND UoM=@UoM";
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", _ITEMCODE));
            oCommand.Parameters.Add(new SqlParameter("@UoM", _UOM));

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(_table);

            if (_table.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public DataTable GetUnitMeasures(string ItemCode)
        {
            DataTable _table = new DataTable();

            SqlConnection oConnection = new SqlConnection();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "SELECT * FROM UnitMeasures WHERE ItemCode=@ItemCode";
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(_table);

            return _table;

        }


        public double GetPackSize(string ItemCode, string UoM)
        {
            DataTable _table = new DataTable();

            SqlConnection oConnection = new SqlConnection();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "SELECT * FROM UnitMeasures WHERE ItemCode=@ItemCode AND UoM=@UoM";
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@UoM", UoM));

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(_table);

            return Convert.ToDouble(_table.Rows[0]["ConvQty"].ToString());

        }
    }
}
